VERSION 5.00
Object = "{67397AA1-7FB1-11D0-B148-00A0C922E820}#6.0#0"; "MSADODC.OCX"
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "comdlg32.ocx"
Begin VB.Form BlkProcess 
   Caption         =   "Bulk Process"
   ClientHeight    =   3090
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   4680
   LinkTopic       =   "Form2"
   MDIChild        =   -1  'True
   ScaleHeight     =   11010
   ScaleWidth      =   20280
   WindowState     =   2  'Maximized
   Begin VB.Frame Frame3 
      Caption         =   "Import Agent Info"
      Height          =   2775
      Left            =   13440
      TabIndex        =   8
      Top             =   2160
      Width           =   5175
      Begin VB.CommandButton btnImport 
         Caption         =   "Import"
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   2040
         TabIndex        =   11
         Top             =   1680
         Width           =   1335
      End
      Begin VB.TextBox flAgentInfo 
         Appearance      =   0  'Flat
         Height          =   285
         Left            =   360
         TabIndex        =   10
         Top             =   360
         Width           =   3135
      End
      Begin VB.CommandButton btnAgent 
         Caption         =   "Choose..."
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   3480
         TabIndex        =   9
         Top             =   360
         Width           =   1335
      End
   End
   Begin VB.Frame Frame2 
      Caption         =   "Change Introducer"
      Height          =   2775
      Left            =   6960
      TabIndex        =   4
      Top             =   2160
      Width           =   5175
      Begin VB.CommandButton btnIntroOpen 
         Caption         =   "Choose..."
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   3480
         TabIndex        =   7
         Top             =   360
         Width           =   1335
      End
      Begin VB.TextBox flIntro 
         Appearance      =   0  'Flat
         Height          =   285
         Left            =   360
         TabIndex        =   6
         Top             =   360
         Width           =   3135
      End
      Begin VB.CommandButton btnChngIntro 
         Caption         =   "Change Introducer"
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   1680
         TabIndex        =   5
         Top             =   1680
         Width           =   1815
      End
   End
   Begin MSAdodcLib.Adodc Adodc1 
      Height          =   330
      Left            =   1320
      Top             =   6240
      Width           =   1200
      _ExtentX        =   2117
      _ExtentY        =   582
      ConnectMode     =   0
      CursorLocation  =   3
      IsolationLevel  =   -1
      ConnectionTimeout=   15
      CommandTimeout  =   30
      CursorType      =   3
      LockType        =   3
      CommandType     =   8
      CursorOptions   =   0
      CacheSize       =   50
      MaxRecords      =   0
      BOFAction       =   0
      EOFAction       =   0
      ConnectStringType=   1
      Appearance      =   1
      BackColor       =   -2147483643
      ForeColor       =   -2147483640
      Orientation     =   0
      Enabled         =   -1
      Connect         =   ""
      OLEDBString     =   ""
      OLEDBFile       =   ""
      DataSourceName  =   ""
      OtherAttributes =   ""
      UserName        =   ""
      Password        =   ""
      RecordSource    =   ""
      Caption         =   "Adodc1"
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      _Version        =   393216
   End
   Begin VB.Frame Frame1 
      Caption         =   "Delete Renewals"
      Height          =   2895
      Left            =   1080
      TabIndex        =   0
      Top             =   2040
      Width           =   5175
      Begin VB.CommandButton btnDelProcess 
         Caption         =   "Delete Renewals"
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   495
         Left            =   1680
         TabIndex        =   3
         Top             =   1920
         Width           =   1815
      End
      Begin MSComDlg.CommonDialog flDialog 
         Left            =   5640
         Top             =   4800
         _ExtentX        =   847
         _ExtentY        =   847
         _Version        =   393216
      End
      Begin VB.TextBox flName 
         Appearance      =   0  'Flat
         Height          =   285
         Left            =   360
         TabIndex        =   2
         Top             =   600
         Width           =   3135
      End
      Begin VB.CommandButton btnOpen 
         Caption         =   "Choose..."
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   3480
         TabIndex        =   1
         Top             =   600
         Width           =   1335
      End
   End
End
Attribute VB_Name = "BlkProcess"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim rs As Recordset
Dim db As Connection
Dim recSet As Recordset
Dim strCon As ADODB.Connection
Dim dbCon As Connection
Dim reSet As Recordset
Dim dueNo As Integer

Private Sub btnAgent_Click()
flDialog.ShowOpen
flAgentInfo.Text = flDialog.fileName
End Sub

Private Sub btnChngIntro_Click()
If flIntro.Text <> "" Then
Set strCon = GetExcelConnection(flIntro.Text)

Set db = New Connection
db.Provider = "Microsoft.Jet.OLEDB.4.0;"
db.Open App.Path & "/Jeevan.mdb"

Set rs = New Recordset
rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic

Set recSet = New Recordset
recSet.CursorLocation = adUseServer
recSet.CursorType = adOpenKeyset
recSet.LockType = adLockOptimistic

rs.Open "Select * from [Sheet1$]", strCon
'MsgBox (rs.RecordCount)
rs.MoveFirst
While Not rs.EOF
recSet.Open "Update AgentInfo Set Pos='" & rs.Fields(2) & "', IntroNo='" & rs.Fields(1) & "' where CodeNo = '" & rs.Fields(0) & "'", db
recSet.Open "Update Salary Set Pos='" & rs.Fields(2) & "', IntroCode='" & rs.Fields(1) & "' where CodeNo = '" & rs.Fields(0) & "'", db
rs.MoveNext
Wend
MsgBox "Updated Successfully"
End If
End Sub

Private Sub btnDelProcess_Click()
If flName.Text <> "" Then
Set strCon = GetExcelConnection(flName.Text)

Set db = New Connection
db.Provider = "Microsoft.Jet.OLEDB.4.0;"
db.Open App.Path & "/Jeevan.mdb"

Set rs = New Recordset
rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic

Set recSet = New Recordset
recSet.CursorLocation = adUseServer
recSet.CursorType = adOpenKeyset
recSet.LockType = adLockOptimistic

rs.Open "Select * from [Sheet1$]", strCon
'MsgBox (rs.RecordCount)
rs.MoveFirst
While Not rs.EOF
'MsgBox (rs.Fields(0))
recSet.Open "Select * from Customer where JVM='" & rs.Fields(0) & "'", db

Call database(recSet!PlanNo)
reSet.Open "Select * from " & recSet!PlanNo & recSet!mop & " where JVM='" & recSet!JVM & "'", dbCon
dueNo = currentdue()
For i = 2 To reSet.Fields().Count - 1
If IsNull(reSet.Fields(i)) Then
reSet.Fields(i - 1) = Null
reSet.Fields(i - 2) = Null
reSet.Update
mop = Switch(recSet!mop = "Mly", dueNo + 1, recSet!mop = "Qly", dueNo * 3 + 1, recSet!mop = "Hly", dueNo * 6 + 1, recSet!mop = "Yly", dueNo * 12 + 1)
Call senior(recSet!DistributorCode, -Val(recSet!Inst), mop)
Exit For
End If
Next i
reSet.Close
dbCon.Close
recSet.Close
rs.MoveNext
Wend
MsgBox "Records Deleted Successfully"
End If
End Sub

Private Sub btnImport_Click()
If flAgentInfo.Text <> "" Then
Dim qryString As String
qryString = ""
Set strCon = GetExcelConnection(flAgentInfo.Text)

Set db = New Connection
db.Provider = "Microsoft.Jet.OLEDB.4.0;"
db.Open App.Path & "/Jeevan.mdb"

Set rs = New Recordset
rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic

Set recSet = New Recordset
recSet.CursorLocation = adUseServer
recSet.CursorType = adOpenKeyset
recSet.LockType = adLockOptimistic

rs.Open "Select * from [AgentInfo$]", strCon
rs.MoveFirst
While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
qryString = qryString + "'" + CStr(rs.Fields(i)) + "',"
Next i
recSet.Open "Select * from AgentInfo where CodeNo='" & rs.Fields(2) & "'", db
If recSet.RecordCount = 0 Then
recSet.Close
recSet.Open "Insert into AgentInfo Values(" & Mid(qryString, 1, Len(qryString) - 1) & ")", db
recSet.Open "Update AgentInfo Set Perform =0 where CodeNo = '" & rs.Fields(2) & "'", db
recSet.Open "Insert into Salary(Name, Pos, CodeNo, IntroCode) Values('" & rs.Fields(0) & "','" & rs.Fields(1) & "','" & rs.Fields(2) & "','" & rs.Fields(3) & "')", db
Else
recSet.Close
End If
rs.MoveNext
qryString = ""
Wend
MsgBox "Records Updated Successfully"
End If
End Sub

Private Sub btnIntroOpen_Click()
flDialog.ShowOpen
flIntro.Text = flDialog.fileName
End Sub

Private Sub btnOpen_Click()
flDialog.ShowOpen
flName.Text = flDialog.fileName
End Sub

Private Sub Form_Load()
flName.Enabled = False
flIntro.Enabled = False
flAgentInfo.Enabled = False
Adodc1.Visible = False
flDialog.DialogTitle = "Choose Excel File"
flDialog.Filter = "Excel (*.xls)|*.xls"
End Sub

Private Function GetExcelConnection(ByVal Path As String, _
    Optional ByVal Headers As Boolean = False) As Connection
    Dim strConn As String
    Dim objConn As ADODB.Connection
    Set objConn = New ADODB.Connection
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & Path & ";" & _
              "Extended Properties=""Excel 12.0 Xml;HDR=" & _
              IIf(Headers, "Yes", "No") & """"
    objConn.Open strConn
    Set GetExcelConnection = objConn
End Function

Private Function database(ByVal plnNo As String)
Set dbCon = New Connection
dbCon.Provider = "Microsoft.Jet.OLEDB.4.0"
dbCon.Open App.Path & "/" & plnNo & ".mdb"
Set reSet = New Recordset
reSet.LockType = adLockOptimistic
reSet.CursorLocation = adUseServer
reSet.CursorType = adOpenKeyset
End Function

Private Function currentdue() As Integer
For i = 2 To reSet.Fields().Count - 1
If IsNull(reSet.Fields(i)) Then
currentdue = (i / 2) - 1
Exit For
End If
Next i
End Function

